package com.liziing.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liziing.dto.ClassroomInfoReqDTo;
import com.liziing.dto.ClassroomRespDTO;
import com.liziing.dto.ClassroomStatusReqDTO;
import com.liziing.pojo.ClassRoom;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import org.apache.ibatis.annotations.Param;


public interface ClassRoomMapper extends BaseMapper<ClassRoom> {
    @Select("SELECT classroom_id, classroom_name FROM classroom_information")
    List<ClassRoom> getClassRoom_List_Data();

    List<ClassRoom> search(@Param("query") ClassroomStatusReqDTO classRoom);

    @Select("SELECT AVG(capacity) AS AVG, " +
            "MAX(capacity) AS MAX, " +
            "MIN(capacity) AS MIN, " +
            "(SELECT COUNT(*) AS COUNT " +
            "   FROM classroom_information " +
            "   WHERE capacity > (SELECT AVG(capacity) FROM classroom_information)) AS count " +
            "FROM classroom_information \n")
    ClassroomRespDTO searchAvgInfo();

    @Select("SELECT b.classroom_name AS classroomName, c.class_name AS className, d.course_name AS courseName, e.name AS userName\n" +
            "FROM classroom_usage AS a\n" +
            "JOIN classroom_information AS b ON a.classroom_id = b.classroom_id\n" +
            "JOIN class_information AS c ON a.class_id = c.class_id\n" +
            "JOIN course_information AS d ON a.course_id = d.course_id\n" +
            "JOIN users AS e ON a.teacher_id = e.id\n" +
            "WHERE a.classroom_id = #{classroomId};\n")
    ClassroomInfoReqDTo getClassroomInfo(String classroomId);
}
